﻿// =================================================================== 
// 项目说明
//====================================================================
// 
// 文件： SYS_DEPT_ROLES.cs
// 项目名称：

// 创建时间：2013/3/19
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommonLib.Model;
using CommonLib.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  .
    /// </summary>
    public partial class SYS_DEPT_ROLESDAL : ISYS_DEPT_ROLESDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取SYS_DEPT_ROLES表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的


        private const string QueryDataString = " SELECT *,(SELECT DEPT_NAME FROM SYS_DEPT A WHERE A.DEPT_ID=SYS_DEPT_ROLES.DEPT_ID) DEPT_NAME ,(SELECT ROLE_NAME FROM SYS_ROLES A WHERE A.ROLE_ID=SYS_DEPT_ROLES.ROLE_ID) ROLE_NAME  FROM SYS_DEPT_ROLES ";


        public DataSet GetListAllRole(string sDeptId, string sOder)
        {
            string sqlStr = " SELECT ROLE_ID, ROLE_NAME, NOTES  FROM SYS_ROLES WHERE ROLE_ID NOT IN (SELECT ROLE_ID FROM SYS_DEPT_ROLES WHERE DEPT_ID='" + sDeptId + "') ";

            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

        public DataSet GetListAllRole2(string sDeptId,string where, string sOder)
        {
            string sqlStr = " SELECT ROLE_ID, ROLE_NAME, NOTES  FROM SYS_ROLES WHERE ROLE_ID NOT IN (SELECT ROLE_ID FROM SYS_DEPT_ROLES WHERE DEPT_ID='" + sDeptId + "') ";
            sqlStr += where;
            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

    }
}
